package com.gsmr.controller;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import com.gsmr.entity.FScan;
import com.gsmr.entity.PageBean;
import com.gsmr.entity.TCH;
import com.gsmr.entity.TCH_Signaling;
import com.gsmr.service.DecodingService;
import com.gsmr.service.InterferenceQueryService;
import com.gsmr.service.TCHService;
import com.gsmr.service.Train_infoService;
import com.gsmr.util.DateUtil;
import com.gsmr.util.ExcelUtil;
import com.gsmr.util.FormatDate;
import com.gsmr.util.PropertityUtil;
import com.gsmr.util.ResponseUtil;
import com.gsmr.util.StringUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * TCH Controller层
 */
@Controller
@RequestMapping("/tch")
public class TCHController {
	@Resource
	private TCHService tchService;

	@Resource
	private DecodingService decodingService;

	@Resource
	private InterferenceQueryService interferencequeryService;

	@RequestMapping("/findMessageType")
	public void findHDLC(@RequestParam(value = "messageType") String messageType, @RequestParam(value = "UUID") String UUID, @RequestParam(value = "timeStamp") String timeStamp, HttpServletResponse response) throws Exception {

		Object messageContent = null;

		Map<String, Object> map = new HashMap<>();
		map.put("messageType", messageType);
		map.put("UUID", UUID);
		if (timeStamp.equals(DateUtil.dateToString(new Date(), "yyyyMMdd"))) { // 当日数据
			System.out.println("查TCH当日数据");
			List<String> threadId = tchService.findDecodeTable(map); // 先去线程表查数据
			for (String index : threadId) {
				map.put("table_name", messageType.toLowerCase() + "_" + index);
				map.put("threadId", index);
				System.out.println("查询map: " + map);
				List<Object> threadMessage = tchService.findMessageByThread(map);
				if (threadMessage != null && threadMessage.size() > 0) {
					messageContent = threadMessage.get(0);
					break;
				}
			}
		} else { // 不是当日数据
			System.out.println("查TCH非当日数据" + timeStamp);
			map.put("table_name", messageType.toLowerCase() + "_" + timeStamp);
			map.put("timeStamp", timeStamp);
			System.out.println("查询map: " + map);
			List<Object> datas = tchService.findTCHFramedata(map);
			if (datas != null && datas.size() > 0) {
				messageContent = datas.get(0);
			}
		}
		System.out.println("messageContent: " + messageContent);
		JSONObject result = new JSONObject();
		result.put("messageContent", messageContent);
		ResponseUtil.write(response, result);
	}

	/**
	 * 分页条件查询TCH
	 */
	@RequestMapping("/list")
	public void list(@RequestParam(value = "page", required = false) String page, @RequestParam(value = "rows", required = false) String rows, @RequestParam(value = "startTime", required = false) String startTime,
		@RequestParam(value = "endTime", required = false) String endTime, @RequestParam(value = "IMSI", required = false) String IMSI, @RequestParam(value = "MSISDN", required = false) String MSISDN, 
		@RequestParam(value = "CTCS_ID", required = false) String CTCS_ID, @RequestParam(value = "UmType", required = false) String UmType, @RequestParam(value = "district", required = false) String district, 
		@RequestParam(value = "kilometerStart", required = false) String kilometerStart, @RequestParam(value = "kilometerEnd", required = false) String kilometerEnd, @RequestParam(value = "search", required = false) String search, 
		@RequestParam(value = "increase", required = false) String increase, @RequestParam(value = "recordId", required = false) String recordId, @RequestParam(value = "direction", required = false) String direction, TCH tch, HttpServletResponse response) throws Exception {

		if (search == null)
			return;
/*
		Map<String, Object>map1 = new HashMap<>();
		map1.put("table_name", "tch_20190828");
		long time = 1566921600000L;
		SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for (int i = 0; i < 48; i++) {
			long begin = time + i * 1800000;
			long end = time + (i+1) * 1800000;
			map1.put("begin", begin);
			map1.put("end", end);
			int a = tchService.findCTCS_ID(map1);
			System.out.print(ft.format(new Date(begin)) + " ~ " + ft.format(new Date(end)) + ": ");
			System.out.println(a);
		}
		
		
		map1.put("table_name", "tch_20190829");
		time = 1567008000000L;
		
		for (int i = 0; i < 48; i++) {
			long begin = time + i * 1800000;
			long end = time + (i+1) * 1800000;
			map1.put("begin", begin);
			map1.put("end", end);
			int a = tchService.findCTCS_ID(map1);
			System.out.print(ft.format(new Date(begin)) + " ~ " + ft.format(new Date(end)) + ": ");
			System.out.println(a);
		}*/
		
		
		
		PageBean pageBean = new PageBean(Integer.parseInt(page), Integer.parseInt(rows));
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("FrontProcessorId", StringUtil.formatLike(tch.getFrontProcessorId())); // 查询TCH名获取
		//IMSI = IMSI + ",460208579000832";
		String IMSI1 = "1";
		String IMSI2 = "1";
		String MSISDN1 = "1";
		String MSISDN2 = "1";
		List<String> traindata = new ArrayList<>();
		map.put("table_name", "train_info");
		if(!IMSI.equals("")) {
			IMSI1 = IMSI;
			map.put("IMSI", IMSI1);
			traindata = tchService.get_msisdn(map);
			if(traindata.size() != 0) {
				MSISDN1 = traindata.get(0);
			}
			CTCS_ID = "1";
		}
		else if(!MSISDN.equals("")) {
			MSISDN1 = MSISDN;
			map.put("MSISDN", MSISDN1);
			traindata = tchService.get_imsi(map);
			if(traindata.size() != 0) {
				IMSI1 = traindata.get(0);
			}
			CTCS_ID = "1";
		}
		else if(!CTCS_ID.equals("")) {
			map.put("CTCS_ID", CTCS_ID);
			traindata = tchService.get_imsi(map);
			if(traindata.size() >= 2) {
				IMSI1 = traindata.get(0);
				IMSI2 = traindata.get(1);
			}
			else if(traindata.size() == 1) {
				IMSI1 = traindata.get(0);
			}
			traindata = tchService.get_msisdn(map);
			if(traindata.size() >= 2) {
				MSISDN1 = traindata.get(0);
				MSISDN2 = traindata.get(1);
			}
			else if(traindata.size() == 1) {
				MSISDN1 = traindata.get(0);
			}
		}
		map.put("IMSI1", IMSI1);
		map.put("IMSI2", IMSI2);
		map.put("MSISDN1", MSISDN1);
		map.put("MSISDN2", MSISDN2);
		map.put("CTCS_ID", CTCS_ID);
		map.put("UmTypeAll", UmType);
		map.put("districtAll", district);
		map.put("kilometerStart", kilometerStart);
		map.put("kilometerEnd", kilometerEnd);
		map.put("start", pageBean.getStart());
		map.put("recordId", recordId);
		if (direction!=null && !direction.equals("")) {
			if (direction.equals("1")) {
				map.put("direction", "1");
			} else if (direction.equals("0")) {
				map.put("direction", "0");
			}
		}

		List<String> table_names = findTableNames(UmType, map, district, startTime, endTime);

		List<TCH_Signaling> TchList = new ArrayList<>();
		List<TCH_Signaling> Tch_SignalingList = new ArrayList<>();

		// 从TCH表中获取数据
		if (table_names != null && table_names.size() > 0) {
			System.out.print("2、TCH业务获取数据！");
			map.put("start", 0);
			map.put("size", pageBean.getPageSize());
			if (increase.equals("1")) { // 页码增加
				for (int i = table_names.size() - 1; i >= 0 && TchList.size() < pageBean.getPageSize(); i--) {
					map.put("table_name", table_names.get(i));
					TchList.addAll(tchService.findSignalingASC(map));
				}
			} else {
				for (int i = 0; i < table_names.size() && TchList.size() < pageBean.getPageSize(); i++) {
					map.put("table_name", table_names.get(i));
					TchList.addAll(tchService.findSignaling(map));
				}
			}
		}
		System.out.println("找到TCH数据：" + TchList.size() + "条");
		Tch_SignalingList.addAll(TchList);

		int total = Integer.MAX_VALUE;
		JSONObject result = new JSONObject();
		TCHController.ListSortSignaling(Tch_SignalingList);
		if (Tch_SignalingList.size() > pageBean.getPageSize() && increase.equals("1")) {
			Tch_SignalingList = Tch_SignalingList.subList(0, pageBean.getPageSize());
		} else if (Tch_SignalingList.size() > pageBean.getPageSize() && increase.equals("0")) {
			Tch_SignalingList = Tch_SignalingList.subList(Tch_SignalingList.size() - pageBean.getPageSize(), Tch_SignalingList.size());
		}

		// 为了避免往前翻页出现第一页小于100条的情况，对该情况做处理
		if (Tch_SignalingList.size() < pageBean.getPageSize() && Tch_SignalingList.size() > 0 && increase.equals("0")) {
			String tmptime1 = Tch_SignalingList.get(0).getTimeStamp();
			map.put("endTime", null);
			map.put("startTime", DateUtil.longToString(Long.parseLong(tmptime1), "yyyyMMdd"));
			table_names.addAll(tchService.findTableNames(map));
			map.put("startTime", null);
			map.put("endTime", tmptime1);
			System.out.println("endTime="+tmptime1);
			// 从TCH表中获取数据
			TchList.clear();
			Tch_SignalingList.clear();
			if (table_names != null) {
				map.put("start", 0);
				map.put("size", pageBean.getPageSize());
				for (int i = table_names.size() - 1; i >= 0 && TchList.size() < pageBean.getPageSize(); i--) {
					String tableName = table_names.get(i);
					map.put("table_name", tableName);
					List<TCH_Signaling> sub_tchList = tchService.findSignalingASC(map);
					TchList.addAll(sub_tchList);
				}
			}

			Tch_SignalingList.addAll(TchList);
			TCHController.ListSortSignaling(Tch_SignalingList);
			if (Tch_SignalingList.size() > pageBean.getPageSize()) {
				Tch_SignalingList = Tch_SignalingList.subList(0, pageBean.getPageSize());
			}
		}
		setTCHData(Tch_SignalingList);
		JSONArray jsonArray = JSONArray.fromObject(Tch_SignalingList);
		result.put("rows", jsonArray);
		result.put("total", total);
		ResponseUtil.write(response, result);
	}
	
	private String headers[] = { "触发时间", "IMSI", "MSISDN", "机车号", "小区名称", "BCCH", "TCH", "时隙号", "帧号", "方向", "消息类型", "消息名称", "链路层", "链路层(ATP->RBC)", "链路层(RBC->ATP)", 
			"网络层", "传输层", "安全层", "应用层", "CRC校验", "T_TRAIN", "LRBG", "公里标", "速度", "数据长度","LEVEL","SNR","QUALITY", "数据源码", "TMSI", "LAC", "CI", "前置机号", "记录号" };

	/**
	 * 导出TCH
	 */
	@RequestMapping("/export")
	public void export(@RequestParam(value = "startTime", required = false) String startTime, @RequestParam(value = "endTime", required = false) String endTime, @RequestParam(value = "IMSI", required = false) String IMSI,
		@RequestParam(value = "MSISDN", required = false) String MSISDN, @RequestParam(value = "CTCS_ID", required = false) String CTCS_ID, @RequestParam(value = "UmType", required = false) String UmType, 
		@RequestParam(value = "district", required = false) String district,@RequestParam(value = "kilometerStart", required = false) String kilometerStart, 
		@RequestParam(value = "kilometerEnd", required = false) String kilometerEnd, @RequestParam(value = "recordId", required = false) String recordId, @RequestParam(value = "direction", required = false) String direction, HttpServletResponse response) throws Exception {

		Map<String, Object> map = new HashMap<String, Object>();

		String IMSI1 = "1";
		String IMSI2 = "1";
		String MSISDN1 = "1";
		String MSISDN2 = "1";
		List<String> traindata = new ArrayList<>();
		map.put("table_name", "train_info");
		if(!IMSI.equals("")) {
			IMSI1 = IMSI;
			map.put("IMSI", IMSI1);
			traindata = tchService.get_msisdn(map);
			if(traindata.size() != 0) {
				MSISDN1 = traindata.get(0);
			}
			CTCS_ID = "1";
		}
		else if(!MSISDN.equals("")) {
			MSISDN1 = MSISDN;
			map.put("MSISDN", MSISDN1);
			traindata = tchService.get_imsi(map);
			if(traindata.size() != 0) {
				IMSI1 = traindata.get(0);
			}
			CTCS_ID = "1";
		}
		else if(!CTCS_ID.equals("")) {
			map.put("CTCS_ID", CTCS_ID);
			traindata = tchService.get_imsi(map);
			if(traindata.size() >= 2) {
				IMSI1 = traindata.get(0);
				IMSI2 = traindata.get(1);
			}
			else if(traindata.size() == 1) {
				IMSI1 = traindata.get(0);
			}
			traindata = tchService.get_msisdn(map);
			if(traindata.size() >= 2) {
				MSISDN1 = traindata.get(0);
				MSISDN2 = traindata.get(1);
			}
			else if(traindata.size() == 1) {
				MSISDN1 = traindata.get(0);
			}
		}
		map.put("IMSI1", IMSI1);
		map.put("IMSI2", IMSI2);
		map.put("MSISDN1", MSISDN1);
		map.put("MSISDN2", MSISDN2);
		map.put("CTCS_ID", CTCS_ID);
		map.put("UmTypeAll", UmType);
		map.put("districtAll", district);
		map.put("kilometerStart", kilometerStart);
		map.put("kilometerEnd", kilometerEnd);
		map.put("recordId", recordId);
		if (direction!=null && !direction.equals("")) {
			if (direction.equals("1")) {
				map.put("direction", "1");
			} else if (direction.equals("0")) {
				map.put("direction", "0");
			}
		}

		List<String> table_names = findTableNames(UmType, map, district, startTime, endTime);
		List<TCH_Signaling> TchList = new ArrayList<TCH_Signaling>();

		// 从TCH表中获取数据
		if (table_names != null && table_names.size() > 0) {
			map.put("start", 0);
			for (int i = table_names.size() - 1; i >= 0 && TchList.size() < ExcelUtil.maxExportRows; i--) {
				map.put("size", ExcelUtil.maxExportRows - TchList.size());
				map.put("table_name", table_names.get(i));
				TchList.addAll(tchService.findSignalingASC(map));
			}
		}
		setTCHData(TchList);
		TCHController.ListSortSignaling(TchList);
		if (TchList.size() > ExcelUtil.maxExportRows) {
			TchList = TchList.subList(0, ExcelUtil.maxExportRows);
		}
		Workbook wb = new HSSFWorkbook();
		ExcelUtil.fillExcelData_TCHSignaling(TchList, wb, headers);
		ResponseUtil.export(response, wb, "TCH业务" + FormatDate.formatDate() + ".xls");
	}

	private void setTCHData(List<TCH_Signaling> Tch_SignalingList) {
		for (TCH_Signaling t : Tch_SignalingList) {
			if(StringUtil.isNotEmpty(t.getSNR())) {
				StringBuffer SNR=new StringBuffer();
				String[] SNRArry = t.getSNR().split(" ");
				for (int i = 0; i < SNRArry.length; i++) {
					{
						double d = Double.parseDouble(SNRArry[i].trim());
						if(d>0) {
//						d=(Math.log10(d))*10;
							d = (double) Math.round(((Math.log10(d)) * 10) * 10) / 10;
							SNRArry[i] = String.valueOf((d));
							System.out.println("SNRArry[" + i + "]:=================" + SNRArry[i]);
							SNR.append(SNRArry[i] + " ");
						}else {
							SNR.append("/ ");
						}
					}
					System.out.println(SNR);
					t.setSNR(String.valueOf(SNR));
				}
			}
			if (t.getSignalType() != null && t.getSignalType().equals("HDLC")) {
				t.setHDLC(t.getSignalName());
			} else {
				t.setHDLC("I Frame");
			}
			if (t.getDirection() != null && t.getDirection().equals("0")) {
				t.setLLayer2(t.getLLayer());
				t.setLLayer("");
			}
		}
	}

	/**
	 * 导出当前页的数据
	 */
	@RequestMapping("/exportcurrentpage")
	public void exportcurrentpage(@RequestParam(value = "data") String data, HttpServletResponse response) throws Exception {
		
		if (data == null || data.equals("null") || data.length() < 2) return;

		JSONArray jsonArray = JSONArray.fromObject(data);
		List<TCH_Signaling> tchSignalingList = (List<TCH_Signaling>) JSONArray.toCollection(jsonArray, TCH_Signaling.class);

		TCHController.ListSortSignaling(tchSignalingList);
		Workbook wb = new HSSFWorkbook();
		ExcelUtil.fillExcelData_TCHSignaling(tchSignalingList, wb, headers);
		ResponseUtil.export(response, wb, "TCH业务" + FormatDate.formatDate() + ".xls");
	}

	/**
	 * 根据参数，寻找符合参数的数据库中表名
	 */
	private List<String> findTableNames(String UmType, Map<String, Object> map, String district, String startTime, String endTime) {

		if (UmType != null && !UmType.equals("")) {
			String[] subUmType = UmType.split(",");
			List<String> subList = Arrays.asList(subUmType);
			map.put("UmType", subList);
		} else {
			map.put("UmType", "");
		}
		if (district != null && !district.equals("")) {
			String[] subdistrict = district.split(",");
			String[] frontID = new String[subdistrict.length];
			for (int i = 0; i < subdistrict.length; i++) {
				if (subdistrict[i].equals(""))
					continue;
				map.put("frontId", subdistrict[i]);
				map.put("table_name", "bs_info");
				frontID[i] = interferencequeryService.get_frontId(map);
			}
			List<String> subList = Arrays.asList(frontID);
			map.put("district", subList);
		} else {
			map.put("district", "");
		}

		Long earlyMorningTime = System.currentTimeMillis() / (1000 * 3600 * 24) * (1000 * 3600 * 24) - TimeZone.getDefault().getRawOffset();
		// 当前时间戳
		Long nowTime = System.currentTimeMillis();
		if (startTime != null && !startTime.equals("NaN")) {
			map.put("startTime", DateUtil.longToString(Long.parseLong(startTime), "yyyyMMdd"));
		} else {
			map.put("startTime", DateUtil.longToString(earlyMorningTime, "yyyyMMdd"));
		}

		if (endTime != null && !endTime.equals("NaN")) {
			map.put("endTime", DateUtil.longToString(Long.parseLong(endTime), "yyyyMMdd"));
		} else {
			map.put("endTime", DateUtil.longToString(nowTime, "yyyyMMdd"));
		}

		List<String> table_names = new ArrayList<>();
		table_names.addAll(tchService.findTableNames(map));

		System.out.println("在fs_signal_overall中查找到： " + table_names);

		if (startTime != null && !startTime.equals("NaN")) {
			map.put("startTime", startTime);
		} else {
			map.put("startTime", earlyMorningTime);
		}
		if (endTime != null && !endTime.equals("NaN")) {
			map.put("endTime", endTime);
		} else {
			map.put("endTime", nowTime);
		}
		return table_names;
	}

	/**
	 * 判断是否存在指定PrimaryKey
	 */
	@RequestMapping("/exisPrimaryKey")
	public void existUserName(String primaryKey, HttpServletResponse response) throws Exception {
		JSONObject result = new JSONObject();
		Map<String, Object> map = new HashMap<String, Object>();
		String frontProcessorId = primaryKey.substring(0, primaryKey.indexOf("-"));
		map.put("table_name", "tch_" + frontProcessorId);
		map.put("PrimaryKey", primaryKey);
		if (tchService.findByPrimaryKey(map) == null) {
			result.put("exist", false);
		} else {
			result.put("exist", true);
		}
		ResponseUtil.write(response, result);
		
	}

	/**
	 * 添加或者修改TCH
	 * @param flag 1 添加 2修改
	 */
	@RequestMapping("/save")
	public void save(TCH tch, HttpServletResponse response, Integer flag) throws Exception {
		int resultTotal = 0; // 操作的记录条数
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("table_name", "tch_" + tch.getFrontProcessorId());
		map.put("PrimaryKey", tch.getPrimaryKey());
		map.put("TimeStamp", tch.getTimeStamp());
		map.put("StartTime", tch.getStartTime());
		map.put("FrontProcessorId", tch.getFrontProcessorId());
		map.put("RecordId", tch.getRecordId());
		map.put("LogicChannel", tch.getLogicChannel());
		map.put("MSISDN", tch.getMSISDN());
		map.put("Direction", tch.getDirection());
		map.put("FrameNumber", tch.getFrameNumber());
		map.put("SiteName", tch.getSiteName());
		map.put("Decode", tch.getDecode());
		map.put("RawMessage", tch.getRawMessage());
		map.put("BCCH", tch.getBCCH());
		map.put("TCHData", tch.getTCHData());
		map.put("IMSI", tch.getIMSI());
		map.put("Kilometer", tch.getKilometer());
		map.put("Speed", tch.getSpeed());
		map.put("CTCS_ID", tch.getCTCS_ID());
		map.put("ENGINE_ID", tch.getENGINE_ID());
		map.put("CI", tch.getCI());
		map.put("LAC", tch.getLAC());
		map.put("MS_TYPE", tch.getMS_TYPE());
		map.put("Message_TYPE", tch.getMessage_TYPE());
		map.put("SourceData", tch.getSourceData());
		map.put("TCH", tch.getTCH());
		map.put("IMEI", tch.getIMEI());
		map.put("TA", tch.getTA());
		map.put("TCH_FRAME", tch.getTCH_FRAME());
		map.put("TimeSlot", tch.getTimeSlot());
		map.put("TMSI", tch.getTMSI());
		map.put("Channel", tch.getChannel());
		map.put("HDLCData", tch.getHDLCData());
		map.put("NPDUInfo", tch.getNPDUInfo());
		map.put("HDLCMessage", tch.getHDLCMessage());
		if (flag == 1) {
			resultTotal = tchService.add(map);
		} else {
			resultTotal = tchService.update(map);
		}
		JSONObject result = new JSONObject();
		if (resultTotal > 0) {
			result.put("success", true);
		} else {
			result.put("success", false);
		}
		ResponseUtil.write(response, result);
	}

	/**
	 * 删除TCH
	 */
	@RequestMapping("/delete")
	public void delete(@RequestParam(value = "primaryKeys") String primaryKeys, HttpServletResponse response) throws Exception {
		String[] primaryKeysStr = primaryKeys.split(",");
		Map<String, Object> map = new HashMap<String, Object>();
		JSONObject result = new JSONObject();
		try {
			for (int i = 0; i < primaryKeysStr.length; i++) {
				String frontProcessorId = primaryKeysStr[i].substring(0, primaryKeysStr[i].indexOf("-"));
				map.put("table_name", "tch_" + frontProcessorId);
				map.put("PrimaryKey", primaryKeysStr[i]);
				tchService.delete(map);
			}
			result.put("success", true);
		} catch (Exception e) {
			result.put("success", false);
			e.printStackTrace();
		}
		ResponseUtil.write(response, result);
	}

	/**
	 * 用模板导出用户
	 */
	@RequestMapping("/exportByTemplate")
	public void exportByTemplate(@RequestParam(value = "ids") String ids, HttpServletResponse response) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		List<TCH> tchList = new ArrayList<TCH>();
		if (ids != null && !ids.equals("null") && !ids.equals("")) {
			List<String> idsList = Arrays.asList(ids.split(","));
			map.put("ids", idsList);
			tchList.clear();
			List<TCH> sub_tchList = null;
			List<String> table_names = tchService.findTableNames(map);
			if (table_names != null) {
				for (int i = 0; i < table_names.size(); i++) {
					sub_tchList = null;
					map.put("table_name", table_names.get(i));
					sub_tchList = tchService.findByIds(map);
					if (sub_tchList != null) {
						tchList.addAll(sub_tchList);
					}
				}
			}
			TCHController.ListSort(tchList);
			Workbook wb = ExcelUtil.fillExcelDataWithTemplate_TCH(tchList, "tchExportTemplate.xls");
			ResponseUtil.export(response, wb, "利用模版导出excel" + FormatDate.formatDate() + ".xls");
		} else {
			tchList.clear();
			List<TCH> sub_tchList = null;
			List<String> table_names = tchService.findTableNames(map);
			if (table_names != null) {
				for (int i = 0; i < table_names.size(); i++) {
					sub_tchList = null;
					map.put("table_name", table_names.get(i));
					sub_tchList = tchService.find(map);
					tchList.addAll(sub_tchList);
				}
			}
			TCHController.ListSort(tchList);
			Workbook wb = ExcelUtil.fillExcelDataWithTemplate_TCH(tchList, "tchExportTemplate.xls");
			ResponseUtil.export(response, wb, "利用模版导出excel" + FormatDate.formatDate() + ".xls");
		}
	}

	/**
	 * 用户上传
	 */
	@RequestMapping("/upload")
	public void upload(HttpServletRequest request, HttpServletResponse response) throws Exception {
		JSONObject result = new JSONObject();
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			// 将当前上下文初始化给 CommonsMutipartResolver （多部分解析器）
			CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
			// 检查form中是否有enctype="multipart/form-data"
			if (multipartResolver.isMultipart(request)) {
				// 将request变成多部分request
				MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
				// 获取multiRequest 中所有的文件名
				Iterator iter = multiRequest.getFileNames();

				while (iter.hasNext()) {
					// 一次遍历所有文件
					MultipartFile file = multiRequest.getFile(iter.next().toString());
					if (file != null) {
						String path = PropertityUtil.UPLOAD_PATH + FormatDate.formatDate() + file.getOriginalFilename();
						// 上传
						File newFile = new File(path);
						file.transferTo(newFile);
						// 写数据进数据库
						POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(newFile));
						HSSFWorkbook wb = new HSSFWorkbook(fs);
						HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
						if (hssfSheet != null) {
							for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
								HSSFRow hssfRow = hssfSheet.getRow(rowNum);
								if (hssfRow == null) {
									continue;
								}
								TCH tch = new TCH();
								tch.setPrimaryKey(ExcelUtil.formatCell(hssfRow.getCell(0)));
								tch.setTimeStamp(ExcelUtil.formatCell(hssfRow.getCell(1)));
								tch.setStartTime(ExcelUtil.formatCell(hssfRow.getCell(2)));
								tch.setFrontProcessorId(ExcelUtil.formatCell(hssfRow.getCell(3)));
								tch.setRecordId(ExcelUtil.formatCell(hssfRow.getCell(4)));
								tch.setLogicChannel(ExcelUtil.formatCell(hssfRow.getCell(5)));
								tch.setMSISDN(ExcelUtil.formatCell(hssfRow.getCell(6)));
								tch.setDirection(ExcelUtil.formatCell(hssfRow.getCell(7)));
								tch.setFrameNumber(ExcelUtil.formatCell(hssfRow.getCell(8)));
								tch.setSiteName(ExcelUtil.formatCell(hssfRow.getCell(9)));
								tch.setDecode(ExcelUtil.formatCell(hssfRow.getCell(10)));
								tch.setRawMessage(ExcelUtil.formatCell(hssfRow.getCell(11)));
								tch.setBCCH(ExcelUtil.formatCell(hssfRow.getCell(12)));
								tch.setTCHData(ExcelUtil.formatCell(hssfRow.getCell(13)));
								tch.setIMSI(ExcelUtil.formatCell(hssfRow.getCell(14)));
								tch.setKilometer(ExcelUtil.formatCell(hssfRow.getCell(15)));
								tch.setSpeed(ExcelUtil.formatCell(hssfRow.getCell(16)));
								tch.setCTCS_ID(ExcelUtil.formatCell(hssfRow.getCell(17)));
								tch.setENGINE_ID(ExcelUtil.formatCell(hssfRow.getCell(18)));
								tch.setCI(ExcelUtil.formatCell(hssfRow.getCell(19)));
								tch.setLAC(ExcelUtil.formatCell(hssfRow.getCell(20)));
								tch.setMS_TYPE(ExcelUtil.formatCell(hssfRow.getCell(21)));
								tch.setMessage_TYPE(ExcelUtil.formatCell(hssfRow.getCell(22)));
								tch.setSourceData(ExcelUtil.formatCell(hssfRow.getCell(23)));
								tch.setTCH(ExcelUtil.formatCell(hssfRow.getCell(24)));
								tch.setIMEI(ExcelUtil.formatCell(hssfRow.getCell(25)));
								tch.setTA(ExcelUtil.formatCell(hssfRow.getCell(26)));
								tch.setTCH_FRAME(ExcelUtil.formatCell(hssfRow.getCell(27)));
								tch.setTimeSlot(ExcelUtil.formatCell(hssfRow.getCell(28)));
								tch.setTMSI(ExcelUtil.formatCell(hssfRow.getCell(29)));
								tch.setChannel(ExcelUtil.formatCell(hssfRow.getCell(30)));
								tch.setHDLCData(ExcelUtil.formatCell(hssfRow.getCell(31)));
								tch.setNPDUInfo(ExcelUtil.formatCell(hssfRow.getCell(32)));
								tch.setHDLCMessage(ExcelUtil.formatCell(hssfRow.getCell(33)));

								map.put("table_name", "tch_" + tch.getFrontProcessorId());
								map.put("PrimaryKey", tch.getPrimaryKey());
								map.put("TimeStamp", tch.getTimeStamp());
								map.put("StartTime", tch.getStartTime());
								map.put("FrontProcessorId", tch.getFrontProcessorId());
								map.put("RecordId", tch.getRecordId());
								map.put("LogicChannel", tch.getLogicChannel());
								map.put("MSISDN", tch.getMSISDN());
								map.put("Direction", tch.getDirection());
								map.put("FrameNumber", tch.getFrameNumber());
								map.put("SiteName", tch.getSiteName());
								map.put("Decode", tch.getDecode());
								map.put("RawMessage", tch.getRawMessage());
								map.put("BCCH", tch.getBCCH());
								map.put("TCHData", tch.getTCHData());
								map.put("IMSI", tch.getIMSI());
								map.put("Kilometer", tch.getKilometer());
								map.put("Speed", tch.getSpeed());
								map.put("CTCS_ID", tch.getCTCS_ID());
								map.put("ENGINE_ID", tch.getENGINE_ID());
								map.put("CI", tch.getCI());
								map.put("LAC", tch.getLAC());
								map.put("MS_TYPE", tch.getMS_TYPE());
								map.put("Message_TYPE", tch.getMessage_TYPE());
								map.put("SourceData", tch.getSourceData());
								map.put("TCH", tch.getTCH());
								map.put("IMEI", tch.getIMEI());
								map.put("TA", tch.getTA());
								map.put("TCH_FRAME", tch.getTCH_FRAME());
								map.put("TimeSlot", tch.getTimeSlot());
								map.put("TMSI", tch.getTMSI());
								map.put("Channel", tch.getChannel());
								map.put("HDLCData", tch.getHDLCData());
								map.put("NPDUInfo", tch.getNPDUInfo());
								map.put("HDLCMessage", tch.getHDLCMessage());
								tchService.add(map);
							}
						}
					}
				}
			}
			result.put("success", "true");
		} catch (Exception e) {
			result.put("success", "false");
			e.printStackTrace();
		}
		ResponseUtil.write(response, result);
	}

	/**
	 * 查找District列表
	 */
	@RequestMapping("/tchDistrictComboList")
	public void tchDistrictComboList(HttpServletResponse response) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		JSONArray jsonArray = new JSONArray();
		JSONObject jsonObject = new JSONObject();
		// System.out.println("开始找sitename" );
		jsonObject.put("id", -1);
		jsonObject.put("name", "");
		jsonArray.add(jsonObject);
		map.put("table_name", "bs_info");
		List<String> bs_names = tchService.findDistricts(map);
		for (int i = 0; i < bs_names.size(); i++) {
			jsonObject.put("id", i);
			// String bs_name=alarmInfoService.get_bs_names();
			// System.out.println("bs_names:" + bs_names.get(i));
			jsonObject.put("name", bs_names.get(i));
			jsonArray.add(jsonObject);

		}
		ResponseUtil.write(response, jsonArray);
	}

	/**
	 * 查找Message_TYPE列表
	 */
	@RequestMapping("/tchMessage_TYPEComboList")
	public void tchMessage_TYPEComboList(HttpServletResponse response) throws Exception {
		JSONArray jsonArray = new JSONArray();
		Map<String, Object> map = new HashMap<String, Object>();
		List<String> table_names = tchService.findTableNames(map);
		List<TCH> tchList = new ArrayList<TCH>();
		if (table_names != null) {
			for (int i = 0; i < table_names.size(); i++) {
				map.put("table_name", table_names.get(i));
				List<TCH> sub_tchList = tchService.findMessage_TYPEs(map);
				tchList.addAll(sub_tchList);
			}
		}

		// 过滤重复的Message_TYPE
		Map message_TYPE_map = new HashMap<>();
		for (int i = 0; i < tchList.size(); i++) {
			message_TYPE_map.put(tchList.get(i).getMessage_TYPE(), 1);
		}
		Iterator<Map.Entry<String, String>> iterator = message_TYPE_map.entrySet().iterator();
		tchList.clear();
		while (iterator.hasNext()) {
			Map.Entry<String, String> entry = iterator.next();
			TCH tch = new TCH();
			tch.setMessage_TYPE(entry.getKey());
			tchList.add(tch);
		}

		JSONArray rows = JSONArray.fromObject(tchList);
		jsonArray.addAll(rows);
		ResponseUtil.write(response, jsonArray);
	}

	/**
	 * 查找前置机列表
	 */
	@RequestMapping("/tchFrontProcessorIdComboList")
	public void tchFrontProcessorIdComboList(HttpServletResponse response) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		JSONArray jsonArray = new JSONArray();
		JSONObject jsonObject = new JSONObject();
		// System.out.println("开始找sitename" );
		jsonObject.put("id", -1);
		jsonObject.put("name", "");
		jsonArray.add(jsonObject);
		map.put("table_name", "bs_info");
		List<String> FrontProcessorIds = tchService.findFrontProcessorIds(map);
		for (int i = 0; i < FrontProcessorIds.size(); i++) {
			jsonObject.put("id", i);
			// String bs_name=alarmInfoService.get_bs_names();
			// System.out.println("FrontProcessorIds:" +
			// FrontProcessorIds.get(i));
			jsonObject.put("name", FrontProcessorIds.get(i));
			jsonArray.add(jsonObject);

		}
		ResponseUtil.write(response, jsonArray);
	}

	/**
	 * 查找IMSI列表
	 */
	@RequestMapping("/tchIMSIComboList")
	public void tchIMSIComboList(HttpServletResponse response) throws Exception {
		JSONArray jsonArray = new JSONArray();
		Map<String, Object> map = new HashMap<String, Object>();
		List<String> table_names = tchService.findTableNames(map);
		List<TCH> tchList = new ArrayList<TCH>();
		if (table_names != null) {
			for (int i = 0; i < table_names.size(); i++) {
				map.put("table_name", table_names.get(i));
				List<TCH> sub_tchList = tchService.findIMSIs(map);
				tchList.addAll(sub_tchList);
			}
		}

		// 过滤重复的IMSI
		Map iMSI_map = new HashMap<>();
		for (int i = 0; i < tchList.size(); i++) {
			iMSI_map.put(tchList.get(i).getIMSI(), 1);
		}
		Iterator<Map.Entry<String, String>> iterator = iMSI_map.entrySet().iterator();
		tchList.clear();
		while (iterator.hasNext()) {
			Map.Entry<String, String> entry = iterator.next();
			TCH tch = new TCH();
			tch.setIMSI(entry.getKey());
			tchList.add(tch);
		}

		JSONArray rows = JSONArray.fromObject(tchList);
		jsonArray.addAll(rows);
		ResponseUtil.write(response, jsonArray);
	}

	/**
	 * 查找MSISDN列表
	 */
	@RequestMapping("/tchMSISDNComboList")
	public void tchMSISDNComboList(HttpServletResponse response) throws Exception {
		JSONArray jsonArray = new JSONArray();
		Map<String, Object> map = new HashMap<String, Object>();
		List<String> table_names = tchService.findTableNames(map);
		List<TCH> tchList = new ArrayList<TCH>();
		if (table_names != null) {
			for (int i = 0; i < table_names.size(); i++) {
				map.put("table_name", table_names.get(i));
				List<TCH> sub_tchList = tchService.findMSISDNs(map);
				tchList.addAll(sub_tchList);
			}
		}

		// 过滤重复的Message_TYPE
		Map mSISDN_map = new HashMap<>();
		for (int i = 0; i < tchList.size(); i++) {
			mSISDN_map.put(tchList.get(i).getMSISDN(), 1);
		}
		Iterator<Map.Entry<String, String>> iterator = mSISDN_map.entrySet().iterator();
		tchList.clear();
		while (iterator.hasNext()) {
			Map.Entry<String, String> entry = iterator.next();
			TCH tch = new TCH();
			tch.setMSISDN(entry.getKey());
			tchList.add(tch);
		}

		JSONArray rows = JSONArray.fromObject(tchList);
		jsonArray.addAll(rows);
		ResponseUtil.write(response, jsonArray);
	}

	/**
	 * 查找CTCS_ID列表
	 */
	@RequestMapping("/tchCTCS_IDComboList")
	public void tchCTCS_IDComboList(HttpServletResponse response) throws Exception {
		JSONArray jsonArray = new JSONArray();
		Map<String, Object> map = new HashMap<String, Object>();
		List<String> table_names = tchService.findTableNames(map);
		List<TCH> tchList = new ArrayList<TCH>();
		if (table_names != null) {
			for (int i = 0; i < table_names.size(); i++) {
				map.put("table_name", table_names.get(i));
				List<TCH> sub_tchList = tchService.findCTCS_IDs(map);
				tchList.addAll(sub_tchList);
			}
		}

		// 过滤重复的Message_TYPE
		Map cTCS_ID_map = new HashMap<>();
		for (int i = 0; i < tchList.size(); i++) {
			cTCS_ID_map.put(tchList.get(i).getCTCS_ID(), 1);
		}
		Iterator<Map.Entry<String, String>> iterator = cTCS_ID_map.entrySet().iterator();
		tchList.clear();
		while (iterator.hasNext()) {
			Map.Entry<String, String> entry = iterator.next();
			TCH tch = new TCH();
			tch.setCTCS_ID(entry.getKey());
			tchList.add(tch);
		}

		JSONArray rows = JSONArray.fromObject(tchList);
		jsonArray.addAll(rows);
		ResponseUtil.write(response, jsonArray);
	}

	/**
	 * 对List中每个对象元素逆序排序
	 */
	private static void ListSortSignaling(List<TCH_Signaling> list) {
		if (list == null || list.size() < 2) return;
		Collections.sort(list, new Comparator<TCH_Signaling>() {
			@Override
			public int compare(TCH_Signaling sf1, TCH_Signaling sf2) {
				try {
					Long l1 = Long.parseLong(sf1.getTimeStamp());
					Long l2 = Long.parseLong(sf2.getTimeStamp());
					if (l1 < l2) {
						return -1;
					} else if (l1 > l2) {
						return 1;
					} else {
						return 0;
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
				return 0;
			}
		});
	}

	private static void ListSort(List<TCH> list) {
		if (list == null || list.size() < 2) return;
		Collections.sort(list, new Comparator<TCH>() {
			@Override
			public int compare(TCH sf1, TCH sf2) {
				try {
					Long l1 = Long.parseLong(sf1.getTimeStamp());
					Long l2 = Long.parseLong(sf2.getTimeStamp());
					if (l1 > l2) {
						return -1;
					} else if (l1 < l2) {
						return 1;
					} else {
						return 0;
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
				return 0;
			}
		});
	}
}